<?php

// lib_vpdes.php
// routines for interfacing with the VPDES database
// most of these are for contacting the copy of VPDES that is dumped to the WSP databases


function getUserVPDESIDsByWKT($listobject, $wktshape, $oftypes = '', $debug) {
   
   if (is_array($oftypes)) {
      if (count($oftypes) > 0) {
         $oftypes = "'" . join("','", $oftypes) . "'";
      }
   }
   
   if ( (strlen($oftypes) > 0) and ($oftypes <> -1)) {
      $oftypecond = " vpdes_type in ($oftypes) ";
   } else {
      $oftypecond = " ( 1 = 1) ";
   }

     
   $retvals = array();
   $retvals['debug'] = '';
   $listobject->querystring = "  select vpid, vpdes_permit_no, outfall_no, vpdes_type, facility_name ";
   $listobject->querystring .= " from vpdes_one_location ";
   $listobject->querystring .= " WHERE $oftypecond ";
   $listobject->querystring .= "    and contains(geomFromText('$wktshape', 4326), the_geom) ";
   $listobject->querystring .= " GROUP BY vpid, vpdes_permit_no, outfall_no, vpdes_type, facility_name ";
   $listobject->querystring .= " ORDER BY vpdes_permit_no, outfall_no ";
   $retvals['query'] .= " $listobject->querystring ; <br>";
   $listobject->performQuery();
   $retvals['records'] = $listobject->queryrecords;
   
   return $retvals;   
}

function getVPDESAnnualDischargeWKT($vpdes_listobject, $wkt, $startdate = '', $enddate = '') {

   $vpdes_base_query = "  select thisyear, thismonth, round(sum(b.discharge_vpdes_mgd)::numeric,2) as ps_mgd ";
   $vpdes_base_query .= " from ( ";
   $vpdes_base_query .= "    select a.thisyear, b.thismonth, ";
   $vpdes_base_query .= "    (a.thisyear || '-' || b.thismonth || '-01')::date as thisdate, null as thisvalue ";
   $vpdes_base_query .= "    from ( ";
   $vpdes_base_query .= "       select generate_series as thisyear ";
   $vpdes_base_query .= "       from generate_series(1990,extract(year from now())::integer) ";
   $vpdes_base_query .= "    ) as a, ( ";
   $vpdes_base_query .= "       select generate_series as thismonth ";
   $vpdes_base_query .= "       from generate_series(1,12) ";
   $vpdes_base_query .= "    ) as b ";
   $vpdes_base_query .= "    order by a.thisyear, b.thismonth ";
   $vpdes_base_query .= " ) as a left outer join ( ";
   $vpdes_base_query .= "    SELECT mon_startdate, mon_enddate, ";
   $vpdes_base_query .= "       sum(mean_value) as discharge_vpdes_mgd  ";
   //$vpdes_base_query .= "    FROM vpdes_discharge_no_ms4 ";
   $vpdes_base_query .= "    FROM vpdes_discharge_no_ms4_cache ";
   $vpdes_base_query .= "    WHERE contains(geomfromtext('$wkt',4269), setsrid(the_geom,4269)) ";
   // this is hard coded to only grab all outfalls for now
   //$vpdes_base_query .= " AND outfall_no = '$this->vpdes_outfallno' ";
   //$vpdes_base_query .= "       AND mon_startdate >= ( '$startdate'::date - interval '3 months' ) ";
   //$vpdes_base_query .= "       AND mon_startdate <= ( '$enddate'::date + interval '3 months')  ";
   // this is hard coded to only grab water for now
   $vpdes_base_query .= "       AND constit = '001' ";
   $vpdes_base_query .= "    GROUP BY mon_startdate, mon_enddate ";
   $vpdes_base_query .= "    ORDER BY mon_startdate ";
   $vpdes_base_query .= " ) as b ";
   // uses exact date match, screws up on quarterly reporters
   //$vpdes_base_query .= " on (a.thisdate = b.thisdate) ";
   // this one tries to stretch quarterly matches out
   $vpdes_base_query .= " on (a.thisdate >= b.mon_startdate  ";
   $vpdes_base_query .= "    AND a.thisdate <= b.mon_enddate) ";
   //$vpdes_base_query .= " WHERE a.thisdate >= ( '$startdate'::date - interval '3 months' ) ";
   //$vpdes_base_query .= "    AND a.thisdate <= ( '$enddate'::date + interval '3 months') ";
   $vpdes_base_query .= " group by a.thisyear, a.thismonth  ";
   $vpdes_base_query .= " order by a.thisyear, a.thismonth  ";
   $vpdes_listobject->querystring = "create temp table tmp_vpdes_cross as select * from ($vpdes_base_query) as foo where ps_mgd is not null";
   $vpdes_listobject->performQuery();

   $by_month = doGenericCrossTab ($vpdes_listobject, "tmp_vpdes_cross", 'thisyear', 'thismonth', 'ps_mgd', 1, 1);
   $vpdes_listobject->querystring = $by_month;
   $vpdes_listobject->performQuery();
   $vpdes_listobject->show = 0;
   foreach ($vpdes_listobject->queryrecords as $rowkey => $thisrec) {
      $t = 0.0;
      $c = 0;
      for ($i = 1; $i <= 12; $i++) {
         if (strlen($thisrec[$i]) <> 0) {
            $t += $thisrec[$i];
            $c++;
         }
      }
      $a = number_format( ($t / $c), 2);
      $vpdes_listobject->queryrecords[$rowkey]['annual_mgd'] = $a;
   }
   return array('query'=>$vpdes_base_query, 'records' => $vpdes_listobject->queryrecords);

}

?>